using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.MakeIT.Laundy.Agromotor.Site.Entidades;

namespace MakeIT.MakeIT.Laundy.Agromotor.Site.Negocios
{
	public partial class AgromotorsiteDAO : DAO
	{

		public static BannerlateralVO getBannerlateral(int? idBannerLateral)
		{
			BannerlateralVO bannerlateralVO = null;
			List<BannerlateralVO> listBannerlateralVOs = getBannerlaterals(idBannerLateral, null);
			if (listBannerlateralVOs != null && listBannerlateralVOs.Count > 0)
			{
				bannerlateralVO = listBannerlateralVOs[0];
			}
			return bannerlateralVO;
		}

		public static List<BannerlateralVO> getBannerlaterals()
		{
			return getBannerlaterals(null, null);
		}

		public static List<BannerlateralVO> getBannerlaterals(int? idBannerLateral, bool? ativo)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT bal.idBannerLateral as bal_idBannerLateral, bal.bannerMime as bal_bannerMime, bal.bannerNome as bal_bannerNome , bal.ordem as bal_ordem, bal.url as bal_url, bal.ativo as bal_ativo, bal.Cadastro as bal_Cadastro, bal.CadastradoPor as bal_CadastradoPor, bal.Edicao as bal_Edicao, bal.EditadoPor as bal_EditadoPor  ");
			query.AppendLine("FROM bannerlateral as bal ");
			if (idBannerLateral != null)
			{
				query.AppendLine(where + "bal.idBannerLateral=@idBannerLateral ");
				where = "AND ";
			}
			if (ativo != null)
			{
				query.AppendLine(where + "bal.ativo=@ativo ");
				where = "AND ";
			}


            query.AppendLine("ORDER BY bal.ordem ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			if (idBannerLateral!= null) cmd.Parameters.Add(getParameter(cmd, "@idBannerLateral", idBannerLateral));
			if (ativo!= null) cmd.Parameters.Add(getParameter(cmd, "@ativo", ativo.Value?"S":"N"));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<BannerlateralVO> listBannerlateral = new List<BannerlateralVO>();

				while (reader.Read())
				{
					BannerlateralVO bannerlateralVO = BannerlateralVO.CreateVO();
					bannerlateralVO.idBannerLateral = reader.GetInt32(reader.GetOrdinal("bal_idBannerLateral"));
					if (!reader.IsDBNull(reader.GetOrdinal("bal_bannerMime")))
						bannerlateralVO.bannerMime = reader.GetString(reader.GetOrdinal("bal_bannerMime"));
					if (!reader.IsDBNull(reader.GetOrdinal("bal_bannerNome")))
						bannerlateralVO.bannerNome = reader.GetString(reader.GetOrdinal("bal_bannerNome"));
					bannerlateralVO.ordem = reader.GetInt32(reader.GetOrdinal("bal_ordem"));
                    if (!reader.IsDBNull(reader.GetOrdinal("bal_url")))
                        bannerlateralVO.URL = reader.GetString(reader.GetOrdinal("bal_url"));
                    bannerlateralVO.ativo = reader.GetString(reader.GetOrdinal("bal_ativo")).Equals("S");
					bannerlateralVO.cadastro = reader.GetDateTime(reader.GetOrdinal("bal_Cadastro"));
					bannerlateralVO.cadastradoPor = reader.GetString(reader.GetOrdinal("bal_CadastradoPor"));
					bannerlateralVO.edicao = reader.GetDateTime(reader.GetOrdinal("bal_Edicao"));
					bannerlateralVO.editadoPor = reader.GetString(reader.GetOrdinal("bal_EditadoPor"));


					listBannerlateral.Add(bannerlateralVO);
				}
				reader.Close();
				return listBannerlateral;
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }
        }

		public static void setBannerlateral(BannerlateralVO bannerlateralVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE bannerlateral ");
			query.AppendLine("SET bannerMime=@bannerMime, bannerNome=@bannerNome, ordem=@ordem, url=@url, ativo=@ativo,  Cadastro=@Cadastro, CadastradoPor=@CadastradoPor, Edicao=@Edicao, EditadoPor=@EditadoPor ");
			query.AppendLine("WHERE idBannerLateral=@idBannerLateral ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idBannerLateral", bannerlateralVO.idBannerLateral));
			cmd.Parameters.Add(getParameter(cmd, "@bannerMime", bannerlateralVO.bannerMime));
			cmd.Parameters.Add(getParameter(cmd, "@bannerNome", bannerlateralVO.bannerNome));
            cmd.Parameters.Add(getParameter(cmd, "@ordem", bannerlateralVO.ordem));
            cmd.Parameters.Add(getParameter(cmd, "@url", bannerlateralVO.URL));
            cmd.Parameters.Add(getParameter(cmd, "@ativo", (bannerlateralVO.ativo == null ? " " : (bannerlateralVO.ativo.Value ? "S" : "N"))));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", bannerlateralVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", bannerlateralVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", bannerlateralVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", bannerlateralVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}

		public static long newBannerlateral(BannerlateralVO bannerlateralVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO bannerlateral ");
			query.AppendLine("  ( bannerMime, bannerNome, ordem, url, ativo, Cadastro, CadastradoPor, Edicao, EditadoPor ) ");
			query.AppendLine("VALUES ( @bannerMime, @bannerNome, @ordem, @url, @ativo, @Cadastro, @CadastradoPor, @Edicao, @EditadoPor ) ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@bannerMime", bannerlateralVO.bannerMime));
			cmd.Parameters.Add(getParameter(cmd, "@bannerNome", bannerlateralVO.bannerNome));
            cmd.Parameters.Add(getParameter(cmd, "@ordem", bannerlateralVO.ordem));
            cmd.Parameters.Add(getParameter(cmd, "@url", bannerlateralVO.URL));
            cmd.Parameters.Add(getParameter(cmd, "@ativo", (bannerlateralVO.ativo == null ? " " : (bannerlateralVO.ativo.Value ? "S" : "N"))));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", bannerlateralVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", bannerlateralVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", bannerlateralVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", bannerlateralVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('bannerlateral') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}

		public static void delBannerlateral(BannerlateralVO bannerlateralVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM bannerlateral ");
			query.AppendLine("WHERE idBannerLateral=@idBannerLateral ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idBannerLateral", bannerlateralVO.idBannerLateral));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}
		public static void setBannerlateralBanner(BannerlateralVO bannerlateralVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE bannerlateral ");
			query.AppendLine("SET banner=@banner, bannerMime=@bannerMime, bannerNome=@bannerNome ");
			query.AppendLine("WHERE idBannerLateral=@idBannerLateral ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idBannerLateral", bannerlateralVO.idBannerLateral));

			cmd.Parameters.Add(getParameter(cmd, "@banner", bannerlateralVO.banner));
			cmd.Parameters.Add(getParameter(cmd, "@bannerMime", bannerlateralVO.bannerMime));
			cmd.Parameters.Add(getParameter(cmd, "@bannerNome", bannerlateralVO.bannerNome));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}

		public static void getBannerlateralBanner(BannerlateralVO bannerlateralVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT banner as bal_banner, bannerMime as bal_bannerMime, bannerNome as bal_bannerNome ");
			query.AppendLine("FROM bannerlateral ");
			query.AppendLine("WHERE idBannerLateral=@idBannerLateral ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idBannerLateral", bannerlateralVO.idBannerLateral));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				if (reader.Read())
				{
					bannerlateralVO.banner = getBytesFromBlob(reader, "bal_banner");

					if (!reader.IsDBNull(reader.GetOrdinal("bal_bannerMime")))
						bannerlateralVO.bannerMime = reader.GetString(reader.GetOrdinal("bal_bannerMime"));
					if (!reader.IsDBNull(reader.GetOrdinal("bal_bannerNome")))
						bannerlateralVO.bannerNome = reader.GetString(reader.GetOrdinal("bal_bannerNome"));

				}
				reader.Close();
				return;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
			}
		}

	}
}
